from app import app, db
from flask_script import Manager
from sqlalchemy import or_, and_
from app.models import ResultInterfaceSum, ResultInterfaceDetail, ResultVideoDetail, ResultVideoSum
manager=Manager(app)

@manager.command
def init_db():
    db.drop_all()
    db.create_all() #根据定义创建数据库
    db.session.add(ResultSum("2020-04-18 10:59:19", 10, 9, "http://10.0.0.233:8001/videoRep/2020-05-21/50003_inclusion_yhq2020-05-21-19-38-16.mp4"))
    db.session.add(ResultDetail(1, "http://10.0.0.233:8001/testVideo/50003_inclusion_yhq2020-05-21-19-38-1.mp4", "http://10.0.0.233:8001/videoRep/2020-05-21/50003_inclusion_yhq2020-05-21-19-38-1.mp4", "http://10.0.0.233:8001/imgRep/2020-05-21/50003_inclusion_yhq2020-05-21-19-38-1.jpg", 2, 2, 1))
    db.session.add(ResultDetail(1, "http://10.0.0.233:8001/testVideo/50003_inclusion_yhq2020-05-21-19-38-2.mp4", "http://10.0.0.233:8001/videoRep/2020-05-21/50003_inclusion_yhq2020-05-21-19-38-2.mp4", "http://10.0.0.233:8001/imgRep/2020-05-21/50003_inclusion_yhq2020-05-21-19-38-2.jpg", 2, 2, 1))
    db.session.add(ResultDetail(1, "http://10.0.0.233:8001/testVideo/50003_inclusion_yhq2020-05-21-19-38-3.mp4", "http://10.0.0.233:8001/videoRep/2020-05-21/50003_inclusion_yhq2020-05-21-19-38-3.mp4", "http://10.0.0.233:8001/imgRep/2020-05-21/50003_inclusion_yhq2020-05-21-19-38-3.jpg", 2, 2, 1))
    db.session.commit()

@manager.command
def query():
    # print(f"1: {ResultDetail.query.all()}")
    # print(f"2: {ResultDetail.query.get(2)}")
    # print(f"3: {ResultSum.query.filter_by(total_num=10)}")
    # print(f"4: {ResultSum.query.filter_by(total_num=10).first()}")
    # print(f"5: {ResultDetail.query.order_by(ResultDetail.id.desc()).all()}")
    # print(f"6: {ResultDetail.query.filter(ResultDetail.test_video_url.endswith('1.mp4')).all()}")
    # print(f"7: {ResultDetail.query.filter(or_(ResultDetail.id==1, ResultDetail.id==2)).all()}")
    # print(f"8: {ResultDetail.query.paginate(page=2, per_page=1).items}")
    # print(f"9: {ResultDetail.query.order_by(ResultDetail.id.desc()).paginate(page=3, per_page=1).items}")
    # print(f"10: {ResultSum.query.get(1).test_result}")
    # print(f"11: {ResultDetail.query.get(1).test_datetime}")
    # result_detail=ResultSum.query.one().result_detail[0]
    # result_sum=ResultDetail.query.first().result_sum
    # # print(f"12: type: {type(result_detail)}, value: {result_detail}")
    # # print(f"13: type: {type(result_sum)}, value: {result_sum}")
    # print(f"1: type: {type(result_detail)}, value: {result_detail}, result_detail.event_image_url: {result_detail.event_image_url}")
    # print(f"2: type: {type(result_sum)}, value: {result_sum}, result_sum.detail_url: {result_sum.detail_url}")
    print(f"1: {ResultDetail.query.filter_by(id=1).all()}")
    print(f"2: {ResultDetail.query.filter(ResultDetail.id>1).all()}")

@manager.command
def update():
    print(f"0: {ResultSum.query.get(1).total_num}")
    ResultSum.query.get(1).total_num=100
    db.session.commit()
    print(f"1: {ResultSum.query.get(1).total_num}")
    ResultSum.query.filter_by(id=1).update({"total_num":99})
    db.session.commit()
    print(f"2: {ResultSum.query.get(1).total_num}")

@manager.command
def delete():
    ResultDetail.query.filter_by(id=3).delete()
    db.session.delete(ResultDetail.query.get(2))
    db.session.commit()

@manager.command
def verify():
    print(ResultDetail.query.filter(ResultDetail.id==1).first())
    print(ResultDetail.query.get(1))

@manager.command
def original():
    # print(ResultDetail.query(ResultDetail.test_result).join(ResultSum))
    print(ResultDetail.query.join(ResultSum, ResultSum.id == ResultDetail.id))
    print(db.session.query(ResultDetail.test_result, ResultDetail.event_image_url).join(ResultSum, ResultSum.id == ResultDetail.id).first())
    # print(db.session.execute("select * from result_detail join result_sum on result_detail.id = result_sum.id;"))

@manager.command
def diff():
    print(f"1: Model.query: {ResultDetail.query.filter(ResultDetail.id==1).all()}")
    print(f"2: db.session.query: {db.session.query(ResultDetail).filter(ResultDetail.id==1).all()}")
    print(f"3: count: {ResultDetail.query.all()}")
    instance=ResultDetail(1, "http://10.0.0.233:8000/test_detail?id=1", "video.mp4", "image.jpg", 0, 0, 1)
    db.session.add(instance)
    db.session.commit()
    print(f"4: count: {ResultDetail.query.all()}")


@manager.command
def init_result_interface_sum():
    db.create_all()

if __name__=="__main__":
    manager.run()
